﻿Imports QuanLyBanChau.Chau_bus
Imports QuanLyBanChau.LoaiChau_bus
Imports System.Runtime.InteropServices
Imports System.Data.OleDb

Public Class frmKiemKho

    Private Sub btnThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThoat.Click
        Me.Close()
    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim excel As New ExportToExcel()
        Dim dt As DataTable = DirectCast(gvDanhSachChau.DataSource, DataTable)
        excel.Export(dt, "Danh sach", "Tình Trạng Kho Hàng")
    End Sub

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Dim ofd As New OpenFileDialog()

        Dim path As String = ""


        If ofd.ShowDialog() = DialogResult.OK Then
            path = System.IO.Path.GetFullPath(ofd.FileName)

            Dim ExcelObj As New Microsoft.Office.Interop.Excel.Application()

            Dim theWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing


            Dim strPath As String = path


            theWorkbook = ExcelObj.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing)

            Dim sheets As Microsoft.Office.Interop.Excel.Sheets = theWorkbook.Worksheets

            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
            'Get the reference of second worksheet
            Dim strWorksheetName As String = worksheet.Name
            'Get the name of worksheet.
            Dim conn As New OleDbConnection()
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" & path & "'" & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0"""
            Dim a As String = "SELECT * FROM [" & strWorksheetName & "$]"
            Dim command As New OleDbCommand(a, conn)

            Dim projectactivityDS As New DataSet()

            Dim adapter As New OleDbDataAdapter(command)

            adapter.Fill(projectactivityDS)

            gvDanhSachChau.DataSource = projectactivityDS.Tables(0)
            theWorkbook.Close()
            ExcelObj.Quit()
            conn.Close()


            ' Call the garbage collector to collect and wait for finalizers to finish.
            GC.Collect()
            GC.WaitForPendingFinalizers()

            ' Release the COM objects that have been instantiated.
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets) <> 0
            End While
            GC.Collect()
            GC.WaitForPendingFinalizers()
            Marshal.FinalReleaseComObject(theWorkbook)
            Marshal.FinalReleaseComObject(worksheet)
            Marshal.FinalReleaseComObject(sheets)
            Marshal.FinalReleaseComObject(ExcelObj)
        End If
    End Sub

    Private Sub frmKiemKho_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadData()
    End Sub

    Public Sub LoadData()
        Dim dt As DataTable = New DataTable()
        dt = Get_chauBUS()
        gvDanhSachChau.DataSource = dt
    End Sub
End Class